package com.bupt.ilink.mapper.userManagement;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.bupt.ilink.entity.userManagement.DTO.RoleStatistics;
import com.bupt.ilink.entity.userManagement.DTO.UserMetaResponse;
import com.bupt.ilink.entity.userManagement.DTO.UserTeamResponse;
import com.bupt.ilink.entity.userManagement.UserConnectTeams;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.node.ArrayNode;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface UserConnectTeamsMapper extends BaseMapper<UserConnectTeams> {

//    List<UserTeamResponse> getTeamList(String userId);

//    boolean quitTeam(String userId, Long teamId);

    @Select("SELECT group_id FROM user_connect_teams WHERE user_id = #{userId} AND team_id = #{teamId}")
    Long selectGroupIdByUserIdAndTeamId(@Param("userId") String userId, @Param("teamId") Long teamId);

    @Select("SELECT * FROM user_connect_teams WHERE user_id = #{userId} AND team_id = #{teamId}")
    UserConnectTeams findUsermessageByUserIdAndTeamId(String userId, Long teamId);

    @Select("SELECT group_id FROM user_connect_teams WHERE team_id = #{teamId} LIMIT 1")
    Long findGroupIdByTeamId(Long teamId);

    @Select("SELECT ur.member_management " +
            "FROM user_connect_teams uct " +
            "JOIN user_roles ur ON uct.role_id = ur.role_id " +
            "WHERE uct.user_id = #{userId} " +
            "AND uct.team_id = #{teamId}")
    Boolean getMemberManagementByUserIdAndTeamId(@Param("userId") String userId, @Param("teamId") Long teamId);

    @Select("SELECT ur.role_name " +
            "FROM user_connect_teams uct " +
            "JOIN user_roles ur ON uct.role_id = ur.role_id " +
            "WHERE uct.user_id = #{userId} " +
            "AND uct.team_id = #{teamId}")
    String getRoleNameByUserIdAndTeamId(@Param("userId") String userId, @Param("teamId") Long teamId);

//    @Select("SELECT u.realname AS realName, u.school_id AS school_id, u.user_image AS userImage," +
//            "u.UserMobile AS userMobile, u.email, u.user_college AS userCollege, " +
//            "r.role_name AS roleName, tg.name AS groupName " +
//            "FROM user_connect_teams uct " +
//            "JOIN tb_user u ON uct.user_id = u.User_id " +
//            "JOIN user_roles r ON uct.role_id = r.role_id " +
//            "JOIN team_group tg ON uct.group_id = tg.group_id " +
//            "WHERE uct.team_id = #{teamId} " +
//            "AND (tg.group_id = #{groupId})")
//    List<UserMetaResponse> getGroupMembersByTeamIdAndGroupId(@Param("teamId") Long teamId, @Param("groupId") Long groupId);
    @Select("SELECT u.realname AS realName,u.user_id AS userId, u.school_id AS school_id,  u.user_image AS userImage,u.last_login_at AS lastLoginAt," +
            "u.UserMobile AS userMobile, u.email, u.user_college AS userCollege, " +
            "r.role_name AS TeamRole, tg.name AS GroupName " +
            "FROM user_connect_teams uct " +
            "JOIN tb_user u ON uct.user_id = u.user_id " +
            "JOIN user_roles r ON uct.role_id = r.role_id " +
            "JOIN team_group tg ON uct.group_id = tg.group_id " +
            "WHERE uct.team_id = #{teamId} " +
            "AND (tg.group_id = #{groupId})")
    List<UserMetaResponse> getGroupMembersByTeamIdAndGroupId(@Param("teamId") Long teamId, @Param("groupId") Long groupId);

    @Select("SELECT u.realname AS realName,u.user_id AS userId,u.user_image AS userImage," +
            "r.role_name AS TeamRole " +
            "FROM user_connect_teams uct " +
            "JOIN tb_user u ON uct.user_id = u.user_id " +
            "JOIN user_roles r ON uct.role_id = r.role_id " +
            "JOIN team_group tg ON uct.group_id = tg.group_id " +
            "WHERE uct.team_id = #{teamId} "+
            "AND (tg.group_id = #{groupId})")
    List<UserMetaResponse> getGroupMembersBasicByTeamIdAndGroupId(Long teamId, Long groupId);

    @Delete("DELETE FROM user_connect_teams WHERE user_id = #{userId} AND team_id = #{teamId}")
    int deleteByUserIdInTeam(@Param("userId") String userId, @Param("teamId") Long teamId);

    @Select("SELECT ur.role_name AS roleName, ur.role_id AS roleId,COUNT(uct.role_id) AS roleCount " +
            "FROM user_connect_teams uct " +
            "JOIN user_roles ur ON uct.role_id = ur.role_id " +
            "WHERE uct.team_id = #{teamId} " +
            "GROUP BY ur.role_name,ur.role_id")
    List<RoleStatistics> getRoleStatisticsByTeamId(@Param("teamId") Long teamId);

    @Select("SELECT u.realname AS realName,u.user_id AS userId,u.user_image AS userImage, u.school_id AS school_id,u.UserMobile AS userMobile, u.email," +
            "r.role_name AS TeamRole ,tg.name AS GroupName " +
            "FROM user_connect_teams uct " +
            "JOIN tb_user u ON uct.user_id = u.user_id " +
            "JOIN user_roles r ON uct.role_id = r.role_id " +
            "JOIN team_group tg ON uct.group_id = tg.group_id " +
            "WHERE uct.team_id = #{teamId} ")
    List<UserMetaResponse> getRoleMessage(Long teamId);

    @Select("SELECT u.user_image " +
            "FROM user_connect_teams uct " +
            "JOIN tb_user u ON uct.user_id = u.user_id " +
            "JOIN user_roles r ON uct.role_id = r.role_id " +
            "WHERE uct.role_id = #{roleId} ")
    ArrayNode getUserMessageByRole(Long roleId);

    @Update(
            "UPDATE user_connect_teams " +
                    "SET role_id = #{roleId} " +
                    "WHERE team_id = #{teamId} " +
                    "AND user_id=#{userIds}"
    )
    boolean setUserRole(@Param("teamId") Long teamId, @Param("userIds") String userIds, @Param("roleId") Long roleId);

}
